package com.probos.bosnatuur.monitor;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ProjectDbAdapter {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_PROJECT = "project_id";
    public static final String KEY_TITLE = "title";
    public static final String KEY_DESCRIPTION = "description";
    public static final String KEY_SHAPE = "shape";
    public static final String KEY_NAME = "name";
    public static final String KEY_DATE = "dat_element";
    public static final String KEY_USER = "username";
    
    private static final String TAG = "ProjectDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private static SimpleDateFormat mDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static SimpleDateFormat mOutDateFormat = new SimpleDateFormat("d MMM yyyy");
    private static SimpleDateFormat mOutTimeFormat = new SimpleDateFormat("HH:mm");

    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE_PROJECTS =
        "create table projects (" +
	        "_id integer primary key autoincrement, " +
	        "title text not null, " +
	        "description text not null, " +
	        "element_seq integer not null default 0" +
        ");";
    
    private static final String DATABASE_CREATE_ELEMENTS =
            "create table elements (" + 
            	"_id integer primary key autoincrement, " + 
            	"project_id integer not null, " +
            	"nr integer not null, " +
            	"title text not null, "+ 
            	"description text not null, " +
            	"dat_create text not null default current_timestamp, " +
            	"dat_edit text not null default current_timestamp, " +
            	"dat_element text not null default current_timestamp, " +
            	"username text not null," +
            	"shape text not null " +
            ");";

    private static final String DATABASE_CREATE_CULTHIST =
    	    "create table cultuurhistorie (_id integer primary key autoincrement, " +
    		    "element_id integer not null, " +
    		    "hoofd integer not null, " +
                "sub integer, " +
    		    "functie1 integer, " +
    		    "functie2 integer, " +
    		    "functie3 integer, " +
    		    "validatie text, " +
    		    "aanlegjaar integer, " +
    		    "periode text, " +
    		    "bron text, " +
    		    "visueel integer, " +
            	"datum text not null default current_timestamp, " +
            	"opnemer text not null " +
    	    ");";

    private static final String DATABASE_CREATE_PICTURES =
            "create table pictures (_id integer primary key autoincrement, element_id integer not null, name text not null, description text);";

    private static final String DATABASE_CREATE_USERS =
            "create table users (_id integer primary key autoincrement, name text not null);";

    private static final String DATABASE_CREATE_HOOFDCAT =
            "create table che_hoofdcat (_id integer primary key autoincrement, description text not null);";

    private static final String DATABASE_CREATE_SUBCAT =
            "create table che_subcat (_id integer primary key autoincrement, " +
	            "hoofd integer not null, " +
	            "sub integer not null, " +
	            "description text not null, " +
	            "definition text, " +
	            "function integer, " +
	            "source text " +
	            ");";
    
    private static final String DATABASE_CREATE_FUNCTIE =
            "create table che_functie (_id integer primary key, description text not null);";
    private static final String DATABASE_CREATE_VISUEEL =
            "create table che_visueel (_id integer primary key, description text not null);";
    private static final String DATABASE_CREATE_VALIDATIE =
            "create table che_validatie (_id integer primary key autoincrement, code text not null, description text not null);";

    private static final String UPDATE_ELEMENTS_51 = "ALTER TABLE elements ADD COLUMN shape text;"; 
    private static final String UPDATE_ELEMENTS_52 = "UPDATE elements SET shape = description;"; 
    private static final String UPDATE_ELEMENTS_53 = "UPDATE elements SET description = '';";
    private static final String UPDATE_ELEMENTS_14a = "ALTER TABLE projects ADD COLUMN element_seq integer;"; 
    private static final String UPDATE_ELEMENTS_14b = "ALTER TABLE elements ADD COLUMN nr integer;"; 
    
    private static final String DATABASE_NAME = "data";
    private static final String PROJECTS_TABLE = "projects";
    private static final String ELEMENTS_TABLE = "elements";
    private static final String USERS_TABLE = "users";
    private static final String HOOFDCAT_TABLE = "che_hoofdcat";
    private static final String SUBCAT_TABLE = "che_subcat";
    private static final String FUNCTIE_TABLE = "che_functie";
    private static final String VISUEEL_TABLE = "che_visueel";
    private static final String VALIDATIE_TABLE = "che_validatie";
    private static final String CULT_HIST_TABLE = "cultuurhistorie";
    private static final String PICTURES_TABLE = "pictures";

    private static final int DATABASE_VERSION = 15;

    private final Context mCtx;
    
    public class NodeParser {
        public void parse(Node property, ContentValues values) {
          String prop_name = property.getNodeName();
          
          if (prop_name.equalsIgnoreCase("id")){
          	Long id = new Long (property.getFirstChild().getNodeValue());
          	values.put(KEY_ROWID, id);
          }
          if (prop_name.equalsIgnoreCase("omschrijving")){
          	String description = "";
          	// String description = property.getFirstChild().getNodeValue();
          	NodeList ns = property.getChildNodes();
          	for (int i=0;i<ns.getLength();i++){
          		Node child = ns.item(i);
          		description += child.getNodeValue();
		    }           	
          	values.put(KEY_DESCRIPTION, description);
          }
        }
    }
    
    public class ValidatieParser extends NodeParser {    	
    	@Override
    	public void parse(Node property, ContentValues values) {
    		super.parse(property, values);
    		
	        String prop_name = property.getNodeName();
	        
	        if (prop_name.equalsIgnoreCase("code")){
	        	String value = property.getFirstChild().getNodeValue();
	        	values.put("code", value);
	        }
    	}
    }

    public class SubcatParser extends NodeParser {    	
    	@Override
    	public void parse(Node property, ContentValues values) {
    		super.parse(property, values);
	        String prop_name = property.getNodeName();
	        if (prop_name.equalsIgnoreCase("hoofd")){
	        	Long id = new Long (property.getFirstChild().getNodeValue());
	        	values.put("hoofd", id);
	        }
	        if (prop_name.equalsIgnoreCase("sub")){
	        	Long id = new Long (property.getFirstChild().getNodeValue());
	        	values.put("sub", id);
	        }
	        if (prop_name.equalsIgnoreCase("definitie")){
	        	String value = property.getFirstChild().getNodeValue();
	        	values.put("definition", value);
	        }
	        if (prop_name.equalsIgnoreCase("functie")){
	        	Long value = new Long (property.getFirstChild().getNodeValue());
	        	values.put("function", value);
	        }
	        if (prop_name.equalsIgnoreCase("bron")){
	        	String value = property.getFirstChild().getNodeValue();
	        	values.put("source", value);
	        }
    	}
    }    

    private static class DatabaseHelper extends SQLiteOpenHelper {
    	
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE_PROJECTS);
            db.execSQL(DATABASE_CREATE_ELEMENTS);
            db.execSQL(DATABASE_CREATE_USERS);
            db.execSQL(DATABASE_CREATE_HOOFDCAT);
            db.execSQL(DATABASE_CREATE_SUBCAT);
            db.execSQL(DATABASE_CREATE_FUNCTIE);
            db.execSQL(DATABASE_CREATE_VISUEEL);
            db.execSQL(DATABASE_CREATE_VALIDATIE);
            db.execSQL(DATABASE_CREATE_CULTHIST);
            db.execSQL(DATABASE_CREATE_PICTURES);            
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        	Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
//            db.execSQL("DROP TABLE IF EXISTS projects");
//            onCreate(db);
        		try {
					if (oldVersion < 3) {
						db.execSQL(DATABASE_CREATE_ELEMENTS);
					}
					if (oldVersion < 4) {
						db.execSQL("DROP TABLE IF EXISTS elements");
						db.execSQL(DATABASE_CREATE_ELEMENTS);
					}
					if (oldVersion < 6) {
						db.execSQL(UPDATE_ELEMENTS_51);
						db.execSQL(UPDATE_ELEMENTS_52);
						db.execSQL(UPDATE_ELEMENTS_53);
					}
					if (oldVersion < 7) {
						db.execSQL(DATABASE_CREATE_USERS);
					}
					if (oldVersion < 8) {
						db.execSQL(DATABASE_CREATE_HOOFDCAT);
					}
					if (oldVersion < 10) {
						db.execSQL(DATABASE_CREATE_SUBCAT);
					}
					if (oldVersion < 12) {
						db.execSQL(DATABASE_CREATE_CULTHIST);
					}
					if (oldVersion < 13) {
			            db.execSQL(DATABASE_CREATE_FUNCTIE);
			            db.execSQL(DATABASE_CREATE_VISUEEL);
			            db.execSQL(DATABASE_CREATE_VALIDATIE);
					}
					if (oldVersion < 14) {
						db.execSQL(UPDATE_ELEMENTS_14a);
						db.execSQL(UPDATE_ELEMENTS_14b);
					}
					if (oldVersion < 15) {
						db.execSQL(DATABASE_CREATE_PICTURES);
					}					
				} catch (NumberFormatException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public ProjectDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public ProjectDbAdapter open() throws SQLException {    	
		if (mDbHelper == null) {
			mDbHelper = new DatabaseHelper(mCtx);
			// Log.i(TAG, "new DatabaseHelper");
		}
		mDb = mDbHelper.getWritableDatabase();
		Log.i(TAG, "open");
		return this;
    }
;
    public void close() {
    	mDb.close();
        mDbHelper.close();
        Log.i(TAG, "close");
    }

    public boolean isOpen() {
 		return ((mDbHelper != null) && (mDb != null));
    }
    
    
    public static String DateToDBString(Date datum) {
    	return mDateFormat.format(datum);
    }

    public static Date DBStringToDate(String ds) {
    	Date datum = null;
    	try {
			datum = mDateFormat.parse(ds);
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return datum;
    }

    public static String DateToString(Date datum) {
    	return mOutDateFormat.format(datum);
    }
 
    public static String TimeToString(Date datum) {
    	return mOutTimeFormat.format(datum);
    }
    
    
    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @return rowId or -1 if failed
     */
    public long createProject(String title, String description) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_DESCRIPTION, description);

        return mDb.insert(PROJECTS_TABLE, null, initialValues);
    }

    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteProject(long rowId) {
        return mDb.delete(PROJECTS_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllProjects() {
        return mDb.query(PROJECTS_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_DESCRIPTION}, null, null, null, null, null);
    }

    public Cursor getProjects() {
    	String sql = "select projects._id, projects.title, projects.description, " +
    			"count(elements._id) as aantal, max(elements.dat_element) as datum " +
    			"from projects left join elements on projects._id = elements.project_id " +
    			"group by projects._id, projects.title, projects.description ";
    	return mDb.rawQuery(sql, null);
    }
    
    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchProject(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, PROJECTS_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_DESCRIPTION}, 
            		KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateProject(long rowId, String title, String body) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_DESCRIPTION, body);

        return mDb.update(PROJECTS_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }

    public boolean updateProjectValues(long row_id, ContentValues values) {
        boolean result;
        try {
        	result = mDb.update(PROJECTS_TABLE, values, KEY_ROWID + "=" + row_id, null) > 0;
        } catch (SQLException e) {
        	Log.e(TAG+".updateProjectValues", "SQLException: " + e.getMessage());
        	result = false;
       }
        return result;
    }

    public int newProjectElement(Long project_id) {
    	int result = -1;
    	if (project_id != null) {    		
	        Cursor c = mDb.query(true, PROJECTS_TABLE, new String[] {"element_seq"}, KEY_ROWID + "=" + project_id, null, null, null, null, null);
	        if (c != null) {
	        	int i = c.getColumnIndex("element_seq");
	            if (c.moveToFirst() && i >= 0) {
	            	int nr = c.getInt(i);
	            	nr++;
	                ContentValues args = new ContentValues();
	                args.put("element_seq", nr);
	                if (mDb.update(PROJECTS_TABLE, args, KEY_ROWID + "=" + project_id, null)> 0){
	                	result = nr;                         	
	                }
	            }
	        }
    	}
        return result;
    }
    
    /**
     * Return a Cursor over the list of all elements in a given project
     * 
     * @return Cursor over all selected elements
     */
    public Cursor fetchAllElements(long projId) {
        //return mDb.query(ELEMENTS_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_DESCRIPTION, KEY_SHAPE, "nr"}, KEY_PROJECT + "=" + projId, null, null, null, null);
    	String tables = "("+ELEMENTS_TABLE + " LEFT JOIN " + CULT_HIST_TABLE + " ON " + ELEMENTS_TABLE + "." + KEY_ROWID + "=" + CULT_HIST_TABLE + ".element_id) a" +
    			" LEFT JOIN " + HOOFDCAT_TABLE + " ON " + "a.hoofd=" + HOOFDCAT_TABLE + "." + KEY_ROWID;
        Cursor c = null;
		try {
			c = mDb.query(tables, new String[] {
					"a." + KEY_ROWID + " AS _id", 
					KEY_TITLE,
					"a." + KEY_DESCRIPTION + " AS description",
					KEY_SHAPE, 
					"nr",
					"hoofd",
					"dat_element",
					"datum",
					HOOFDCAT_TABLE + "." + KEY_DESCRIPTION + " AS hoofdcat" },
					KEY_PROJECT + "=" + projId, null, null, null, null);
		} catch (Exception e) {
			// TODO: handle exception
			Log.e(TAG+".fetchAllElements", "SQLException: " + e.getMessage());
		}
        return c;
    }

    /**
     * Return a Cursor positioned at the element that matches the given rowId
     * 
     * @param rowId id of element to retrieve
     * @return Cursor positioned to matching element, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchElement(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, ELEMENTS_TABLE, new String[] {KEY_ROWID, KEY_PROJECT, KEY_TITLE, KEY_DESCRIPTION, KEY_SHAPE, "dat_element"}, 
            		KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    public long createElement(long project_id, int nr, String naam, String tekst, String shape, Date datum, String username, Long new_id) {
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_PROJECT, project_id);
        initialValues.put("nr", nr);
        initialValues.put(KEY_TITLE, naam);
        initialValues.put(KEY_DESCRIPTION, tekst);
        initialValues.put(KEY_SHAPE, shape);
        initialValues.put(KEY_DATE, DateToDBString(datum));
        initialValues.put(KEY_USER, username);
        
        if (new_id != null) initialValues.put("_id", new_id);
         
        long result = -1;
        try {
        	result = mDb.insertOrThrow(ELEMENTS_TABLE, null, initialValues);
        } catch (SQLException e) {
        	Log.e(TAG+".createElement", "SQLException: " + e.getMessage());
        }
        
        return result;
    }
    
    public boolean updateElement(long row_id, String title, String body, String shape) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_DESCRIPTION, body);
        args.put(KEY_SHAPE, shape);
        args.put("dat_edit", DateToDBString(new Date()));

        // return mDb.update(ELEMENTS_TABLE, args, KEY_ROWID + "=" + row_id, null) > 0;
        boolean result;
        try {
        	result = mDb.update(ELEMENTS_TABLE, args, KEY_ROWID + "=" + row_id, null) > 0;
        } catch (SQLException e) {
        	Log.e(TAG+".updateElement", "SQLException: " + e.getMessage());
        	result = false;
       }
        return result;
    }

    public boolean updateElementValues(long row_id, ContentValues values) {
        boolean result;
        try {
        	result = mDb.update(ELEMENTS_TABLE, values, KEY_ROWID + "=" + row_id, null) > 0;
        } catch (SQLException e) {
        	Log.e(TAG+".updateElement", "SQLException: " + e.getMessage());
        	result = false;
       }
        return result;
    }
    
    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteElement(long row_id) {
        return mDb.delete(ELEMENTS_TABLE, KEY_ROWID + "=" + row_id, null) > 0;
    }

    // PICTURES
    
    public Cursor getPictures(long element_id) {
        Cursor mCursor = 
        		mDb.query(true, PICTURES_TABLE, new String[] {KEY_ROWID, "element_id", KEY_NAME, KEY_DESCRIPTION}, 
                		"element_id" + "=" + element_id, null, null, null, null, null);
        return mCursor;
    }
    
    public long insertPicture(long element_id, String naam, String tekst) {
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put("element_id", element_id);
        initialValues.put(KEY_NAME, naam);
        if (tekst != null) initialValues.put(KEY_DESCRIPTION, tekst);
        
        long new_id = -1;
        try {
        	new_id = mDb.insertOrThrow(PICTURES_TABLE, null, initialValues);
        } catch (SQLException e) {
        	Log.e(TAG+".createElement", "SQLException: " + e.getMessage());
        }
        
        return new_id;
    }
    
    public boolean deletePicture(long row_id) {
        return mDb.delete(PICTURES_TABLE, KEY_ROWID + "=" + row_id, null) > 0;
    }

    // USERS
    
    public long createUser(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        return mDb.insert(USERS_TABLE, null, initialValues);
    }

    public boolean deleteUser(long row_id) {
        return mDb.delete(USERS_TABLE, KEY_ROWID + "=" + row_id, null) > 0;
    }
    
    public boolean updateUser(long row_id, String name) {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);
        return mDb.update(USERS_TABLE, args, KEY_ROWID + "=" + row_id, null) > 0;
    }
    
    public Cursor fetchUser(long row_id) throws SQLException {
        Cursor mCursor =
            mDb.query(true, USERS_TABLE, new String[] {KEY_ROWID, KEY_NAME}, 
            		KEY_ROWID + "=" + row_id, null, null, null, null, null);
        if (mCursor != null) mCursor.moveToFirst();
        return mCursor;
    }

    public Cursor fetchAllUsers() throws SQLException {
        return mDb.query(USERS_TABLE, new String[] {KEY_ROWID, KEY_NAME}, null, null, null, null, null);
    }
    
    public Cursor findUser(String name) throws SQLException {
        Cursor mCursor =
            mDb.query(true, USERS_TABLE, new String[] {KEY_ROWID, KEY_NAME}, 
            		KEY_NAME + "='" + name + "'", null, null, null, null, null);
        if (mCursor != null) mCursor.moveToFirst();
        return mCursor;
    }
 
    // CULTUURHISTORIE

    public long createCultuurHistorie(ContentValues values) {    	        
        long new_id = -1;
        try {
        	new_id = mDb.insertOrThrow(CULT_HIST_TABLE, null, values);
        } catch (SQLException e) {
        	Log.e(TAG+".createCultuurHistorie", "SQLException: " + e.getMessage());
        }      
        return new_id;
    }

    public boolean updateCultuurHistorie(long row_id, ContentValues values) {
        boolean result;
        try {
        	result = mDb.update(CULT_HIST_TABLE, values, KEY_ROWID + "=" + row_id, null) > 0;
        } catch (SQLException e) {
        	Log.e(TAG+".updateCultuurHistorie", "SQLException: " + e.getMessage());
        	result = false;
        }
        return result;
    }
    
    public Cursor findCultuurHistorie(long element_id) {
        Cursor c = null;
        try {
			c = mDb.query(true, CULT_HIST_TABLE, null, "element_id="
					+ element_id, null, null, null, null, null);
		} catch (SQLException e) {
        	Log.e(TAG+".findCultuurHistorie", "element_id = " + element_id + "\n" + "SQLException: " + e.getMessage());
		}
		return c;
    }
    
    // HULPTABELLEN
    
    public Cursor HoofdCategorie() throws SQLException {
        return mDb.query(HOOFDCAT_TABLE, new String[] {KEY_ROWID, KEY_DESCRIPTION}, null, null, null, null, null);
    }
    public Cursor SubCategorie(long hoofd) throws SQLException {
        return mDb.query(SUBCAT_TABLE, new String[] {KEY_ROWID, KEY_DESCRIPTION, "hoofd", "sub", "function"}, "hoofd=" + hoofd, null, null, null, "sub");
    }
    public Cursor FunctieCursor() throws SQLException {
        return mDb.query(FUNCTIE_TABLE, new String[] {KEY_ROWID, KEY_DESCRIPTION}, null, null, null, null, null);
    }
    public Cursor VisueelCursor() throws SQLException {
        return mDb.query(VISUEEL_TABLE, new String[] {KEY_ROWID, KEY_DESCRIPTION}, null, null, null, null, null);
    }
    public Cursor ValidatieCursor() throws SQLException {
        return mDb.query(VALIDATIE_TABLE, new String[] {KEY_ROWID, KEY_DESCRIPTION, "code"}, null, null, null, null, null);
    }

    public void LoadTableXML(String table_name, String xml_name, NodeParser p ) {
    	
    	Cursor c = mDb.query(table_name, new String[] {KEY_ROWID}, null, null, null, null, null);
    	boolean leeg = (c.getCount() == 0);
    	c.close();
    	
    	if (leeg) {
 	        try {
	            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();               
	            DocumentBuilder builder = null;        
    			builder = factory.newDocumentBuilder();
    			
            	InputStream s = mCtx.getAssets().open(xml_name + ".xml");
				Document doc = builder.parse( s );
				s.close();
				
				mDb.beginTransaction();
				
                NodeList items = doc.getElementsByTagName(xml_name);
                for (int i=0;i<items.getLength();i++){
                    Node n = items.item(i);
                    NodeList properties = n.getChildNodes();
		            ContentValues values = new ContentValues(); 
                    
                    for (int j=0;j<properties.getLength();j++){
                        Node property = properties.item(j);
                        p.parse(property, values);
                    }
                    
                    mDb.insertOrThrow(table_name, null, values);
                }
		        mDb.setTransactionSuccessful();
	        }
			catch (ParserConfigurationException e) {
				Log.e(TAG+".LoadTableXML: " + xml_name, "ParserConfigurationException: " + e.getMessage());
			}
	        catch (SQLException e) {
	        	Log.e(TAG+".LoadTableXML: " + xml_name, "SQLException: " + e.getMessage());
	        }
	        catch (IOException e) {
	        	Log.e(TAG+".LoadTableXML: " + xml_name, "IOException: " + e.getMessage());
	        }
	        catch (SAXException e) {
	        	Log.e(TAG+".LoadTableXML: " + xml_name, "SAXException: " + e.getMessage());
			}
	        finally {
	        	mDb.endTransaction();
	        	Log.d(TAG+".LoadTableXML: " + xml_name, "OK");
	        }
    	}
    }

    public void LoadHoofdcat() {
    	LoadTableXML(HOOFDCAT_TABLE,"hoofdcat",new NodeParser());
    }
    public void LoadSubcat() {
    	LoadTableXML(SUBCAT_TABLE,"subcat",new SubcatParser());
    }    
    public void LoadFunctie() {
    	// mDb.execSQL("delete from " + FUNCTIE_TABLE + ";");
    	LoadTableXML(FUNCTIE_TABLE,"functie",new NodeParser());
    }
    public void LoadVisueel() {
    	LoadTableXML(VISUEEL_TABLE,"visueel",new NodeParser());
    }
    public void LoadValidatie() {
    	LoadTableXML(VALIDATIE_TABLE,"validatie",new ValidatieParser());
    }
}

/*
    public void LoadHoofdcatX() {
    	
//    	mDb.execSQL("drop table che_hoofdcat;");
//    	mDb.execSQL(DATABASE_CREATE_HOOFDCAT);
    	
    	Cursor c = mDb.query(HOOFDCAT_TABLE, new String[] {KEY_ROWID}, null, null, null, null, null);
    	boolean leeg = (c.getCount() == 0);
    	c.close();
    	
    	if (leeg) {
 	        try {
	            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();               
	            DocumentBuilder builder = null;        
    			builder = factory.newDocumentBuilder();
	        		        
            	InputStream s = mCtx.getAssets().open("hoofdcat.xml");
				Document doc = builder.parse( s );
				s.close();

				mDb.beginTransaction();
				
                NodeList items = doc.getElementsByTagName("hoofdcat");
                for (int i=0;i<items.getLength();i++){
                    Node n = items.item(i);
                    NodeList properties = n.getChildNodes();
		            ContentValues values = new ContentValues(); 
                    
                    for (int j=0;j<properties.getLength();j++){
                        Node property = properties.item(j);
                        String prop_name = property.getNodeName();
                        
                        if (prop_name.equalsIgnoreCase("id")){
                        	Long id = new Long (property.getFirstChild().getNodeValue());
                        	values.put(KEY_ROWID, id);
                        }
                        if (prop_name.equalsIgnoreCase("omschrijving")){
                        	String description = property.getFirstChild().getNodeValue();
                        	values.put(KEY_DESCRIPTION, description);
                        }
                    }
                    
                    mDb.insert(HOOFDCAT_TABLE, null, values);
                }
		        mDb.setTransactionSuccessful();
	        }
			catch (ParserConfigurationException e) {
				Log.e(TAG+".test", "ParserConfigurationException: " + e.getMessage());
			}
	        catch (SQLException e) {
	        	Log.e(TAG+".test", "SQLException: " + e.getMessage());
	        }
	        catch (IOException e) {
	        	Log.e(TAG+".test", "IOException: " + e.getMessage());
	        }
	        catch (SAXException e) {
	        	Log.e(TAG+".test", "SAXException: " + e.getMessage());
			}
	        finally {
	        	mDb.endTransaction();
	        	Log.d(TAG+".LoadHoofdcat", "OK");
	        }
    	}
    }

    public void LoadSubcatX() {
    	
    	Cursor c = mDb.query(SUBCAT_TABLE, new String[] {KEY_ROWID}, null, null, null, null, null);
    	boolean leeg = (c.getCount() == 0);
    	c.close();
    	
    	if (leeg) {
	        try {
	            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();               
	            DocumentBuilder builder = null;        
    			builder = factory.newDocumentBuilder();
	        		        
            	InputStream s = mCtx.getAssets().open("subcat.xml");
				Document doc = builder.parse( s );
				s.close();

				mDb.beginTransaction();
				
                NodeList items = doc.getElementsByTagName("subcat");
                for (int i=0;i<items.getLength();i++){
                    Node n = items.item(i);
                    NodeList properties = n.getChildNodes();
		            ContentValues values = new ContentValues(); 
                    
                    for (int j=0;j<properties.getLength();j++){
                        Node property = properties.item(j);
                        String prop_name = property.getNodeName();
                        
                        if (prop_name.equalsIgnoreCase("hoofd")){
                        	Long id = new Long (property.getFirstChild().getNodeValue());
                        	values.put("hoofd", id);
                        }
                        if (prop_name.equalsIgnoreCase("sub")){
                        	Long id = new Long (property.getFirstChild().getNodeValue());
                        	values.put("sub", id);
                        }
                        if (prop_name.equalsIgnoreCase("omschrijving")){
                        	String value = property.getFirstChild().getNodeValue();
                        	values.put(KEY_DESCRIPTION, value);
                        }
                        if (prop_name.equalsIgnoreCase("definitie")){
                        	String value = property.getFirstChild().getNodeValue();
                        	values.put("definition", value);
                        }
                        if (prop_name.equalsIgnoreCase("functie")){
                        	Long value = new Long (property.getFirstChild().getNodeValue());
                        	values.put("function", value);
                        }
                        if (prop_name.equalsIgnoreCase("bron")){
                        	String value = property.getFirstChild().getNodeValue();
                        	values.put("source", value);
                        }
                    }
                    
                    mDb.insert(SUBCAT_TABLE, null, values);
                }
		        mDb.setTransactionSuccessful();
	        }
			catch (ParserConfigurationException e) {
				Log.e(TAG+".LoadSubcat", "ParserConfigurationException: " + e.getMessage());
			}
	        catch (SQLException e) {
	        	Log.e(TAG+".LoadSubcat", "SQLException: " + e.getMessage());
	        }
	        catch (IOException e) {
	        	Log.e(TAG+".LoadSubcat", "IOException: " + e.getMessage());
	        }
	        catch (SAXException e) {
	        	Log.e(TAG+".LoadSubcat", "SAXException: " + e.getMessage());
			}
	        finally {
	        	mDb.endTransaction();
	        	Log.d(TAG+".LoadSubcat", "OK");
	        }
    	}
    }
 */

